﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using ForumModel;
using Model;

public class Entities
{
    public static Categories[] GetCategories(int parentID)
    {
        using (var context = new MamiMeuEntities())
        {
            var categ = context.Categories
                .Include("Children")
                .Where(c => c.Parent.CategoryID == parentID)
                .OrderBy(c => c.MenuOrdinal);
            return categ.ToArray();
        }
    }

    public static DataTable GetTreeCategories()
    {
        using (var context = new MamiMeuEntities())
        {
            var values = context.TreeViewCategories.Select(c => c);
            if (values.Count() > 0)
            {
                return values.ToDataTable();
            }
        }
        return null;
    }

    public static Categories GetCategoryDetails(int categoryID)
    {
        using (var context = new MamiMeuEntities())
        {
            var categ = context.Categories.Where(c => c.CategoryID == categoryID);
            if (categ.Count() > 0)
            {
                var current = categ.First();
                current.ParentReference.Load();
                var parent = current;
                while (parent.Parent != null)
                {
                    parent = parent.Parent;
                    parent.ParentReference.Load();
                }
                current.ProposedByReference.Load();
                return current;
            }
        }
        return null;
    }

    public static Articles GetArticleDetails(int articleID)
    {
        using (var context = new MamiMeuEntities())
        {
            var art = context.Articles.Where(c => c.ID == articleID);
            if (art.Count() > 0)
            {
                var current = art.First();
                current.CategoryReference.Load();
                var category = current.Category;
                category.ParentReference.Load();
                while (category.Parent != null)
                {
                    category = category.Parent;
                    category.ParentReference.Load();
                }
                current.AuthorReference.Load();
                return current;
            }
        }
        return null;
    }

    public static Categories InsertOrUpdateCategory(int? categoryID, string title, string description, string metaDesc, string metaKeys, string imageFileName, bool approved, bool isFeatured, int? parentID, int? proposedBy, int? menuOrdinal)
    {
        using (var context = new MamiMeuEntities())
        {
            Categories mycateg;
            var categ = context.Categories.Where(c => c.CategoryID == categoryID || c.Title == title);
            if (categoryID.HasValue)
            {
                mycateg = categ.First();
            }
            else
            {
                if (categ.Count() > 0)
                    throw new Exception("Mai exista o categorie cu acelasi titlu. Va rugam sa reincercati!");
                mycateg = new Categories();
            }
            mycateg.Title = title;
            mycateg.Description = description;
            mycateg.MetaDesc = metaDesc;
            mycateg.MetaKeys = metaKeys;
            if (!string.IsNullOrEmpty(imageFileName))
                mycateg.ImagePath = imageFileName;
            mycateg.Approved = approved;
            mycateg.IsFeatured = isFeatured;
            try
            {
                if (parentID.HasValue)
                    mycateg.Parent = context.Categories.Where(c => c.CategoryID == parentID.Value).First();
            }
            catch
            { throw new Exception("Parent"); }
            try
            {
                if (proposedBy.HasValue)
                    mycateg.ProposedBy = context.Users.Where(c => c.UserID == proposedBy.Value).First();
            }
            catch
            { throw new Exception("author"); }
            if (menuOrdinal.HasValue)
                mycateg.MenuOrdinal = menuOrdinal.Value;

            if (!categoryID.HasValue)
                context.AddToCategories(mycateg);
            context.SaveChanges();

            return !categoryID.HasValue
                       ? context.Categories.Where(a => a.Title == mycateg.Title && a.Description == mycateg.Description).First()
                       : mycateg;
        }
    }

    public static Articles InsertOrUpdateArticle(int? articleID, string title, string content, int categoryID, DateTime datePosted, string metaDesc, string metaKeys, string imageFileName, bool approved, bool featured, string featuredImageFileName)
    {
        using (var context = new MamiMeuEntities())
        {
            Articles myArticle;
            var art = context.Articles.Where(a => a.ID == articleID || a.Title == title);
            if (articleID.HasValue)
            {
                myArticle = art.First();
            }
            else
            {
                if (art.Count() > 0)
                    throw new Exception("Mai exista un articol cu acelasi titlu. Va rugam incercati altul!");

                myArticle = new Articles { Views = 0, Votes = 0, Rank = 0 };
            }
            myArticle.Title = title;
            myArticle.Content = content;
            myArticle.DatePosted = datePosted;
            myArticle.MetaDesc = metaDesc;
            myArticle.MetaKeys = metaKeys;
            myArticle.Approved = approved;
            if (!string.IsNullOrEmpty(imageFileName))
                myArticle.ImagePath = imageFileName;
            myArticle.Category = context.Categories.Where(c => c.CategoryID == categoryID).First();
            myArticle.Author = context.Users.Where(c => c.UserID == 2).First();

            //extra properties

            myArticle.IsFeatured = featured;
            if (!string.IsNullOrEmpty(featuredImageFileName))
                myArticle.FeaturedImage = featuredImageFileName;

            //add or save changes
            if (!articleID.HasValue)
                context.AddToArticles(myArticle);
            context.SaveChanges();

            return articleID.HasValue
                       ? myArticle
                       : context.Articles.Where(a => a.Title == myArticle.Title).First();
        }
    }

    public static ChildrenNames InsertOrUpdateBabyName(int? nameID, string name, string description, string origin, int sex)
    {
        using (var context = new MamiMeuEntities())
        {
            ChildrenNames myName;
            var art = context.ChildrenNames.Where(a => a.ID == nameID || a.Nume == name);
            if (nameID.HasValue)
            {
                myName = art.First();
            }
            else
            {
                if (art.Count() > 0)
                    throw new Exception("Mai exista acest nume in baza de date. Va rugam adaugati altul!");
                myName = new ChildrenNames();
            }
            myName.Nume = name;
            myName.Description = description;
            myName.Origin = origin;
            myName.Sex = Convert.ToBoolean(sex);

            //add or save changes
            if (!nameID.HasValue)
                context.AddToChildrenNames(myName);
            context.SaveChanges();

            return !nameID.HasValue
                       ? myName
                       : context.ChildrenNames.Where(a => a.Nume == myName.Nume).First();
        }
    }

    //public static Articles ArticleAdd(string title, string content, int categoryID, DateTime datePosted, string metaDesc, string metaKeys, string imageFileName, bool approved)
    //{
    //    using (var context = new MamiMeuEntities())
    //    {
    //        Articles art = new Articles();
    //        art.Title = title;
    //        art.Content = content;
    //        art.DatePosted = datePosted;
    //        art.MetaDesc = metaDesc;
    //        art.MetaKeys = metaKeys;
    //        art.Views = 0;
    //        art.Votes = 0;
    //        art.Approved = approved;
    //        if (!string.IsNullOrEmpty(imageFileName))
    //            art.ImagePath = imageFileName;
    //        art.Rank = 0;
    //        art.Category = context.Categories.Where(c => c.CategoryID == categoryID).First();
    //        art.Author = context.Users.Where(c => c.UserID == 1).First();

    //        context.AddToArticles(art);
    //        context.SaveChanges();

    //        return context.Articles.Where(a => a.Category == art.Category && a.Title == art.Title && a.DatePosted == art.DatePosted).First();
    //    }
    //}

    public static Articles[] GetArticlesByCategory(int categoryID)
    {
        using (var context = new MamiMeuEntities())
        {
            var categ = context.Articles
                .Include("Category")
                .Where(a => a.Approved && a.Category.CategoryID == categoryID);
            if (categ.Count() > 0)
            {
                return categ.ToArray();
            }
        }
        return null;
    }

    public static Articles[] GetRelatedArticles(Articles article)
    {
        using (var context = new MamiMeuEntities())
        {
            var articles = context.Articles
                .Where(a => a.Approved
                            && a.Category.CategoryID == article.Category.CategoryID
                            && a.ID != article.ID)
                .OrderByDescending(a => a.DatePosted);
            if (articles.Count() > 0)
            {
                var gen = new Random();
                return articles.Skip(gen.Next(0, 5)).Take(10).ToArray();
            }
        }
        return null;
    }

    public static Articles[] GetLatestArticles(int count)
    {
        using (var context = new MamiMeuEntities())
        {
            return context.Articles
                .Where(a => a.Approved)
                .OrderByDescending(c => c.DatePosted)
                .Take(count)
                .ToArray();
        }
    }

    public static Articles[] SearchArticles(string searchPrefix)
    {
        using (var context = new MamiMeuEntities())
        {
            var art = context.Articles.Where(c => c.Title.Contains(searchPrefix) || c.Title == searchPrefix);

            return (art.Count() > 0) ? art.ToArray() : null;
        }
    }

    public static Articles[] GetFeaturedArticles(int count)
    {
        using (var context = new MamiMeuEntities())
        {
            return context.Articles
                .Where(a => a.IsFeatured.Value && a.Approved && !string.IsNullOrEmpty(a.FeaturedImage))
                .OrderByDescending(c => c.DatePosted)
                .Take(count)
                .ToArray();
        }
    }

    public static Categories GetFeaturedCategory()
    {
        using (var context = new MamiMeuEntities())
        {
            return context.Categories.Where(a => a.Approved && a.IsFeatured.Value).First();
        }
    }

    public static ArrayList GetNamesOrigins()
    {
        using (var context = new MamiMeuEntities())
        {
            IEnumerable<string> origins = context.ChildrenNames.OrderBy(t => t.Origin).Select(t => t.Origin).Distinct();
            var ret = new ArrayList();
            foreach (var value in origins)
            {
                ret.Add(value);
            }
            return ret;
        }
    }

    public static Categories[] SearchCategories(string searchPrefix)
    {
        using (var context = new MamiMeuEntities())
        {
            var categ = context.Categories.Where(c => c.Title.Contains(searchPrefix) || c.Title == searchPrefix);

            return (categ.Count() > 0) ? categ.ToArray() : null;
        }
    }

    public static ChildrenNames[] SearchNames(int sex, string origin, string prefix)
    {
        using (var context = new MamiMeuEntities())
        {
            bool sexBool = Convert.ToBoolean(sex);
            return !string.IsNullOrEmpty(origin)
                       ? (!string.IsNullOrEmpty(prefix)
                              ? context.ChildrenNames
                                    .Where(a => a.Sex == sexBool && a.Origin == origin && a.Nume.StartsWith(prefix))
                                    .Select(c => c)
                                    .ToArray()
                              : context.ChildrenNames
                                    .Where(a => a.Sex == sexBool && a.Origin == origin)
                                    .Select(c => c).ToArray())
                       : (!string.IsNullOrEmpty(prefix)
                              ? context.ChildrenNames
                                    .Where(a => a.Sex == sexBool && a.Nume.StartsWith(prefix))
                                    .Select(c => c)
                                    .ToArray()
                              : context.ChildrenNames
                                    .Where(a => a.Sex == sexBool)
                                    .Select(c => c)
                                    .ToArray());
        }
    }

    public static DataTable GetRecentPostList()
    {
        using (ForumEntities context = new ForumEntities())
        {
            var posts = from s in context.cs_Sections
                        join p in context.cs_Posts.Include("cs_Threads") on s.SectionID equals p.SectionID into subset
                        from ss in subset
                        where ss.PostMedia == 0
                        orderby ss.PostDate descending
                        select new
                        {
                            PostID = ss.PostID,
                            ThreadID = ss.cs_Threads.ThreadID,
                            PostAuthor = ss.PostAuthor,
                            UserID = ss.UserID,
                            Subject = ss.Subject,
                            Body = ss.Body,
                            SectionName = s.Name,
                            PostDate = ss.PostDate
                        };
            return posts.Take(10).ToDataTable();
        }
    }

    public static Articles RateArticle(int articleID, int rate)
    {
        using (var context = new MamiMeuEntities())
        {
            Articles myArticle = context.Articles.Where(a => a.ID == articleID).First();
            myArticle.Rank = (myArticle.Rank * myArticle.Votes + rate) / ++myArticle.Votes;
            context.SaveChanges();
            return myArticle;
        }
    }

    public static Articles[] GetArticlesByTag(string tag)
    {
        using (var context = new MamiMeuEntities())
        {
            return context.Articles
                .Where(a => a.MetaKeys.Contains(tag) && a.Approved)
                .OrderByDescending(c => c.DatePosted)
                .OrderBy(c => c.Title)
                .ToArray();
        }
    }

}